﻿using Coldairarrow.Entity.Base;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.IT;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.TD;
using Coldairarrow.Util;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Threading.Tasks;

namespace Coldairarrow.Business.IT
{
    public partial class IT_LocalDetailBusiness : BaseBusiness<IT_LocalDetail>, IIT_LocalDetailBusiness, ITransientDependency
    {
        public IT_LocalDetailBusiness(IDbAccessor db)
            : base(db)
        {
        }

        #region 外部接口

        public async Task<PageResult<IT_LocalDetail>> GetDataListAsync(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<IT_LocalDetail>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<IT_LocalDetail, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }

            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task<IT_LocalDetail> GetTheDataAsync(string id)
        {
            return await GetEntityAsync(id);
        }

        public async Task AddDataAsync(IT_LocalDetail data)
        {
            await InsertAsync(data);
        }

        public async Task UpdateDataAsync(IT_LocalDetail data)
        {
            await UpdateAsync(data);
        }

        public async Task DeleteDataAsync(List<string> ids)
        {
            await DeleteAsync(ids);
        }
       

        public async Task<PageResult<IT_LocalDetailDTO>> GetWarehouseList(PageInput<IT_LocalDetailConditionDTO> input) {
            Expression<Func<IT_LocalDetail, Base_Department, TD_InStorage, PB_Location, PB_Tray, PB_TrayZone,
   PB_Material, Base_UnitItems, PB_PackList, PB_StorArea, PB_Rack, Base_User, PB_PackList, IT_LocalDetailDTO>> select = (a, b, c, d, e, f, g, h, p, i, x, y, w) => new IT_LocalDetailDTO
   {
       StorIdName = b.Name,
       LocalIdName = d.Name,
       TrayIdName = e.Name,
       ZoneIdName = f.Name,//托盘分区名称
       MaterialIdName = g.Name,//物料名称
       UnitemName = h.Name,//单位名称       
       Price = g.Price,//单价
       Amount = a.Num * g.Price,//总额
       MaterSpec = g.Spec,
       StoareaName = i.Name,//货区
       RackName = x.Name,
       UserName = y.RealName,
       MaterialCode = g.Code,
       PackId = w.Id,
       PackIdName = w.BoxName,
       Nums=w.Nums,
    
   };
            var search = input.Search;
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<IT_LocalDetail>().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.StorId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<TD_InStorage>() on a.InStorId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Location>() on a.LocalId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_Tray>() on a.TrayId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    join f in Db.GetIQueryable<PB_TrayZone>() on a.ZoneId equals f.Id into af
                    from f in af.DefaultIfEmpty()
                    join g in Db.GetIQueryable<PB_Material>() on a.MaterialId equals g.Id into ag
                    from g in ag.DefaultIfEmpty()
                    join h in Db.GetIQueryable<Base_UnitItems>() on g.storageId equals h.Id into gh
                    from h in gh.DefaultIfEmpty()
                    join p in Db.GetIQueryable<PB_PackList>() on a.PackId equals p.Id into ap
                    from p in ap.DefaultIfEmpty()
                    join i in Db.GetIQueryable<PB_StorArea>() on d.AreaId equals i.Id into di
                    from i in di.DefaultIfEmpty()
                    join x in Db.GetIQueryable<PB_Rack>() on d.RackId equals x.Id into dx
                    from x in dx.DefaultIfEmpty()
                    join y in Db.GetIQueryable<Base_User>() on a.CreatorId equals y.Id into ay
                    from y in ay.DefaultIfEmpty()
                    join w in Db.GetIQueryable<PB_PackList>() on a.PackId equals w.Id into aw
                    from w in aw.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e, f, g, h, p, i, x, y,w);
            if (!search.StoareaName.IsNullOrEmpty())
            {
                q = q.Where(m => m.StoareaName == search.StoareaName);
            }
            if (!search.RackName.IsNullOrEmpty())
            {
                q = q.Where(m => m.RackName == search.RackName);
            }
            if (!search.PackIdName.IsNullOrEmpty())
            {
                q = q.Where(m => m.PackIdName .Contains(search.PackIdName));
            }
            var list = q.GroupBy(x => new { x.StorId, x.MaterialId, x.LocalId, x.InType }).Select
            (x => new { StorId = x.Key.StorId, MaterialId = x.Key.MaterialId, LocalId = x.Key.LocalId, InType = x.Key.InType, Num = x.Sum(i => i.Num) });
           
            return await q.GetPageResultAsync(input);
        }

        /// <summary>
        /// 库存明细
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public  async Task<PageResult<IT_LocalDetailDTO>> GetDataListAsyncDtoList(PageInput<IT_LocalDetailConditionDTO> input)
        {
            Expression<Func<IT_LocalDetail, Base_Department, TD_InStorage,PB_Location, PB_Tray, PB_TrayZone,
PB_Material,Base_UnitItems, PB_PackList,PB_StorArea,PB_Rack,Base_User,IT_LocalDetailDTO>> select = (a, b, c, d, e,f,g,h,p,i,x,y) => new IT_LocalDetailDTO
{
             StorIdName=b.Name,//仓库名称
             LocalIdName=d.Name,//货位名称
             TrayIdName=e.Name,//托盘名称
              ZoneIdName=f.Name,//托盘分区名称
              MaterialIdName=g.Name,//物料名称
              UnitemName=h.Name,//单位名称
             //暂时写死 数据库中的数据不对    SELECT  *  FROM Base_EnumItem WHERE `Code`='Product'   SELECT  InType  FROM TD_InStorage
              InType = "Product",//入库类类型
              Price=g.Price,//单价
              Amount =a.Num*g.Price,//总额
              MaterSpec=g.Spec,
              StoareaName=i.Name,//货区
              RackName=x.Name,
              UserName=y.RealName,
    MaterialCode =g.Code
};
            var search = input.Search;
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<IT_LocalDetail>().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.StorId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<TD_InStorage>() on a.InStorId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Location>() on a.LocalId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_Tray>() on a.TrayId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    join f in Db.GetIQueryable<PB_TrayZone>() on a.ZoneId equals f.Id into af
                    from f in af.DefaultIfEmpty()
                    join g in Db.GetIQueryable<PB_Material>() on a.MaterialId equals g.Id into ag
                    from g in ag.DefaultIfEmpty()
                    join h in Db.GetIQueryable<Base_UnitItems>() on g.storageId equals h.Id into gh
                    from h in gh.DefaultIfEmpty()
                    join p in Db.GetIQueryable<PB_PackList>() on a.PackId equals p.Id into ap
                    from p in ap.DefaultIfEmpty()
                    join i in Db.GetIQueryable<PB_StorArea>() on d.AreaId equals i.Id into di
                    from i in di.DefaultIfEmpty()
                    join x in Db.GetIQueryable<PB_Rack>() on d.RackId equals x.Id into dx
                    from x in dx.DefaultIfEmpty()
                    join y in Db.GetIQueryable<Base_User>() on a.CreatorId equals y.Id into ay
                    from y in ay.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e, f, g, h, p,i,x,y);
            if (!search.LocalName.IsNullOrEmpty())
            {
                q = q.Where(X => X.LocalIdName.Contains(search.LocalName));
            }
            if (!search.MaterialName.IsNullOrEmpty()) {
                q = q.Where(m=>m.MaterialIdName.Contains(search.MaterialName));
            }
            if (!search.MaterialName.IsNullOrEmpty())
            {
                q = q.Where(m => m.MaterialIdName.Contains(search.MaterialName));
            }
            if (!search.Code.IsNullOrEmpty())
            {
                q = q.Where(m => m.MaterialCode==search.Code);
            }
            if (!search.InTimeStart.IsNullOrEmpty() && !search.InTimeEnd.IsNullOrEmpty())
            {
                q = q.Where(x => (search.InTimeStart <= x.InTime) && (x.InTime <= search.InTimeEnd));
            }
          q.GroupBy(x => new { x.StoareaName, x.MaterialIdName, x.LocalIdName ,x.InType}).Select
                (x => new { StorId = x.Key.StoareaName, MaterialIdName = x.Key.MaterialIdName, LocalIdName = x.Key.LocalIdName, InType=x.Key.InType, Num = x.Sum(i => i.Num) }).ToList();
          
            return await q.GetPageResultAsync(input);
        }
        public async Task<PageResult<IT_LocalDetailDtolist>> IT_LocalDetailList(PageInput<IT_LocalDetailDtolistContionDTO> input)
        {
            string sql = @"SELECT
	department.Id AS deptId,
	department.`Name` AS StorIdName,
	StorArea.`Name` AS StoareaName,
	Rack.`Name` AS RackName,
	Location.`Name` AS LocalIdName,
	LocalDetail.MaterialId,
    LocalDetail.LocalId,
	LocalDetail.StorId,
material.`Id` AS materialId,
	material.`Code` AS materialCode,
	material.`Name` AS MaterialIdName,
	material.Spec AS MaterSpec,
	IFNULL( SUM( LocalDetail.Num ), 0 ) Num,
	UnitItems.`Name` AS UnitemName,
    material.Weight,
	(SUM( LocalDetail.Num )*material.Weight) as TotalWeight
FROM
	IT_LocalDetail AS LocalDetail
	INNER JOIN Base_Department AS department ON LocalDetail.StorId = department.Id
	INNER JOIN PB_Location AS Location ON LocalDetail.LocalId = Location.Id
	INNER JOIN PB_Rack AS Rack ON Location.RackId = Rack.Id
	INNER JOIN PB_StorArea AS StorArea ON Location.AreaId = StorArea.Id
	INNER JOIN PB_Material AS material ON LocalDetail.MaterialId = material.Id
	INNER JOIN Base_UnitItems AS UnitItems ON material.storageId = UnitItems.Id
	INNER JOIN Base_User AS Users ON LocalDetail.CreatorId = Users.Id 
WHERE 
	1=1 {0}{1}{2}{3}{4}
GROUP BY
	LocalDetail.MaterialId,
   department.Id,
	Location.Id
	ORDER BY  LocalDetail.MaterialId,LocalDetail.LocalId
	";
            string StorId = "";
            string LocalName = "";
            string MaterialName = "";

            string StorArea = "";
            string RackId = "";
            if (!string.IsNullOrEmpty(input.Search.StorId))
            {
                StorId = "AND LocalDetail.StorId like '%" + input.Search.StorId + "%'";
            }
            if (!string.IsNullOrEmpty(input.Search.StorAreaId))
            {
                StorArea = "And StorArea.Id LIKE '%" + input.Search.StorAreaId + "%'";
            }
            if (!string.IsNullOrEmpty(input.Search.LocalName))
            {
                LocalName = "AND Location.`Name`  like '%" + input.Search.LocalName + "%' ";
            }
            if (!string.IsNullOrEmpty(input.Search.RackId))
            {
                RackId = "AND  Rack.`Id`  like '%" + input.Search.RackId + "%' ";
            }
            if (!string.IsNullOrEmpty(input.Search.MaterialName))
            {
                MaterialName = "And  (material.`Name`  like '%" + input.Search.MaterialName + "%' OR material.`Code`  like '%" + input.Search.MaterialName + "%' ) ";
            }
            sql = string.Format(sql, StorId, StorArea, RackId, LocalName, MaterialName);
            var res = await Db.GetListBySqlAsync<IT_LocalDetailDtolist>(sql.ToString());
            #region   分页处理数据   有bug 没有按照主数据进行处理
            int pagecount;//总页数
            List<IT_LocalDetailDtolist> listpro_down = res.ToList();//一个要分页的，类型数据集合，存储的是所有的数据
            List<IT_LocalDetailDtolist> productes = new List<IT_LocalDetailDtolist>();//用来存储要展示的数据
            int pagees = listpro_down.Count;//所有数据的总条数
                                            //计算总页数
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();//分页的重点
            return new PageResult<IT_LocalDetailDtolist> { Data = productes, Total = pagees };
            #endregion


        }


        public async  Task  <List<IT_LocalDetailDtolist>> GetDataListAsynclist() {
            string sql = @"SELECT
	department.Id AS deptId,
	material.`Name` AS MaterialIdName,
	material.Spec AS MaterSpec,
	LocalDetail.StorId,
	LocalDetail.MaterialId,
	material.Id AS mid ,
IFNULL(material.Price ,0) AS Price,
	StorArea.`Name` AS StoareaName,
	StorArea.Id,
	Rack.Id,
	Rack.`Name` AS RackName,
	Location.`Name` AS LocalIdName,
  IFNULL(SUM(LocalDetail.Num), 0) Num,
  IFNULL(SUM(LocalDetail.Num*material.Price), 0) Amount,
	department.`Name` AS StorIdName,
	UnitItems.`Name` AS UnitemName,
	Users.RealName AS UserName,
MAX(LocalDetail.CreateTime)  AS InTime
 FROM
	IT_LocalDetail AS LocalDetail
	INNER JOIN Base_Department AS department ON LocalDetail.StorId = department.Id
	INNER JOIN PB_Location AS Location ON LocalDetail.LocalId = Location.Id
	INNER JOIN PB_Rack AS Rack ON Location.RackId = Rack.Id
	INNER JOIN PB_StorArea AS StorArea ON Location.AreaId = StorArea.Id
	INNER JOIN PB_Material AS material ON LocalDetail.MaterialId = material.Id
	INNER JOIN Base_UnitItems AS  UnitItems ON material.storageId=UnitItems.Id
	INNER JOIN Base_User as Users on LocalDetail.CreatorId=Users.Id
	WHERE   LocalDetail.Id IS NOT NULL  
	GROUP BY LocalDetail.StorId , LocalDetail.MaterialId,StorArea.Id,Rack.Id,Location.Id,LocalDetail.CreatorId
	order by material.`Code`      
	";
           List< IT_LocalDetailDtolist >res = await Db.GetListBySqlAsync<IT_LocalDetailDtolist>(sql);
            return res;

        }
        #endregion

        #region 私有成员

        #endregion
    }
}